import frappe
from frappe import _
from frappe.utils import (
    cint,
    cstr,
    flt,
    getdate,
    datetime,
    get_first_day,
    get_last_day,
    formatdate,
)
from erpnext.accounts.report.trial_balance.trial_balance import (
    get_opening_balances,
)

from erpnext.accounts.utils import get_balance_on
from datetime import datetime
import pandas as pd


@frappe.whitelist()
def getAccountBalance():
    user = frappe.session.user
    print("Current user:", user)
    start_date = datetime.strptime("2023-12-31", "%Y-%m-%d")
    end_date = datetime.strptime("2024-12-31", "%Y-%m-%d")
    acct_list = []
    doc = frappe.get_doc("AC_BalanceSheet_Config", "明天国际")    
    for item in doc.items:
        # print(item.acct)
        opening_balance = get_balance_on(item.acct, start_date)
        closing_balance = get_balance_on(item.acct, end_date)
        acct_list.append(
            {
                "报表项目": item.rpt_item,
                "科目": item.acct,
                "期初": opening_balance * item.rate,
                "期末": closing_balance * item.rate,
                "序号":item.sort_id
            }
        )
    # 从字典创建DataFrame
    df = pd.DataFrame.from_dict(acct_list)
    print(df)
    summary = df.groupby("报表项目").agg({"期初": "sum", "期末": "sum","序号":"max"}).reset_index().sort_values(by="序号")
    print(summary)
    # temp = df[["科目","期初","期末"]].rename(columns={'科目': '报表项目'})
    # summary1 = pd.concat([summary,temp]).reset_index(drop=True)
    # print(summary1)
    childs = []
    for index, row in summary.iterrows():
        childs.append(            
            {
                "rpt_item": row["报表项目"],
                "opening_balance": row["期初"],
                "closing_balance": row["期末"],
                "sort_id": row["序号"]
            },
        )
    new_bs = frappe.get_doc(
        {
            "doctype": "AC_BalanceSheet",
            "company": doc.company,
            "fiscal_year": doc.fiscal_year,            
            "childs": childs
        }
    )   
    result = new_bs.insert(ignore_permissions=True)
    frappe.db.commit()
    return result.as_dict()
